package cn.kgc.dao.impl;

import cn.kgc.dao.BaseDao;
import cn.kgc.dao.StudentDao;
import cn.kgc.entity.Student;
import cn.kgc.entity.Teacher;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class StudentImpl extends BaseDao implements StudentDao {
    @Override
    public List<Student> searchs(int currentPage,int currentSize,String stunum,String name,String specialty) {
        String sql="SELECT * FROM school_student limit ?,? ";
        String names = "%"+name+"%";
        String specialtys = "%"+specialty+"%";
        if((stunum==null||"".equals(stunum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql="SELECT * FROM school_student limit ?,? ";
            return this.query(Student.class,sql,(currentPage-1)*currentSize,currentSize);
        }
        if((stunum!=null&&!"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
            sql = "select * from school_student where stunum=? and name like '"+names+"' and specialty like '"+specialtys+"' limit ?,?";
            return this.query(Student.class,sql,stunum,(currentPage-1)*currentSize,currentSize);
        }
        if((stunum!=null&&!"".equals(stunum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "select * from school_student where stunum=? limit ?,?";
            return this.query(Student.class,sql,stunum,(currentPage-1)*currentSize,currentSize);
        }
        if((stunum==null||"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "select * from school_student where name like '"+names+"' limit ?,?";
            return this.query(Student.class,sql,(currentPage-1)*currentSize,currentSize);
        }
        if((stunum==null||"".equals(stunum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
            sql = "select * from school_student where specialty like '"+specialtys+"' limit ?,?";
            return this.query(Student.class,sql,(currentPage-1)*currentSize,currentSize);
        }
        if((stunum!=null&&name!=null&&!"".equals(stunum)&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
            sql = "select * from school_student where stunum=? and name like '"+names+"' limit ?,?";
            return this.query(Student.class,sql,stunum,(currentPage-1)*currentSize,currentSize);

        }
        if((stunum!=null&&!"".equals(stunum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
            sql = "select * from school_student where stunum=? and specialty like '"+specialtys+"' limit ?,?";
            return this.query(Student.class,sql,stunum,(currentPage-1)*currentSize,currentSize);
        }
        if((stunum==null||"".equals(stunum))&&(name!=null&&specialty!=null&&!"".equals(name)&&!"".equals(specialty))){
            sql = "select * from school_student where name like '"+names+"' and specialty like '"+specialtys+"' limit ?,?";
            return this.query(Student.class,sql,(currentPage-1)*currentSize,currentSize);

        }
        return null;

    }
    //    计算总的记录数
    @Override
    public int totalCount(String stunum,String name,String specialty) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        String names = "%"+name+"%";
        String specialtys = "%"+specialty+"%";
        int Count = 0;
        try {
            con = this.getConnection();
            if((stunum==null||"".equals(stunum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "select Count(*) from school_student";
            }
            if((stunum!=null&&!"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                sql = "select Count(*) from school_student where stunum=? and name like '"+names+"' and specialty like '"+specialtys+"'";
            }
            if((stunum!=null&&!"".equals(stunum))&&(name==null||"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "select Count(*) from school_student where stunum=?";
            }
            if((stunum==null||"".equals(stunum))&&(name!=null&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "select Count(*) from school_student where name like '"+names+"'";
            }
            if((stunum==null||"".equals(stunum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                sql = "select Count(*) from school_student where specialty like '"+specialtys+"'";
            }
            if((stunum!=null&&name!=null&&!"".equals(stunum)&&!"".equals(name))&&(specialty==null||"".equals(specialty))){
                sql = "select Count(*) from school_student where stunum=? and name like '"+names+"'";
            }
            if((stunum!=null&&!"".equals(stunum))&&(name==null||"".equals(name))&&(specialty!=null&&!"".equals(specialty))){
                sql = "select Count(*) from school_student where stunum=? and specialty like '"+specialtys+"'";
            }
            if((stunum==null||"".equals(stunum))&&(name!=null&&specialty!=null&&!"".equals(name)&&!"".equals(specialty))){
                sql = "select Count(*) from school_student where name like '"+names+"' and specialty like '"+specialtys+"'";
            }
            ps = con.prepareStatement(sql);
            if(stunum!=null&&!"".equals(stunum)){
                ps.setObject(1,stunum);
            }
            rs = ps.executeQuery();
            if(rs.next()){
                Count = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            this.closeAll(rs,ps,null,con);
        }
        return Count;

    }

    //计算总页数
    @Override
    public int totalPage(int pageSize,String stunum,String name,String specialty){
        int totalCount = this.totalCount(stunum,name,specialty);
        int CountPage = 0;
        if(totalCount%pageSize==0){
            CountPage = totalCount/pageSize;
        }else {
            CountPage = (totalCount/pageSize)+1;
        }
        return CountPage;
    }

    @Override
    public int add(Student student) {
        String sql="INSERT INTO school_student (stunum, name, gender, age, nation, birthday,grade,specialty,classes ,politicstatus, address, phone)\n" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";
        int Cont = this.execute(sql, student.getStunum(), student.getName(), student.getGender(), student.getAge(), student.getNation(), student.getBirthday(), student.getGrade(),
                student.getSpecialty(), student.getClasses(), student.getPoliticstatus(), student.getAddress(), student.getPhone());
        return Cont;
    }

    //获取选中教师信息 进行回显
    @Override
    public Student getById(String ids) {
        String sql = " select * from school_student where id = ?";
        List<Student> list = this.query(Student.class, sql, ids);
        return list.get(0);
    }
    //修改教师信息
    @Override
    public int updateStudent(Student student) {
        String sql = "update school_student set stunum = ?,name = ?,gender = ?,age = ?,nation = ?,birthday = ?,grade = ?,specialty = ?,classes = ?"+
                ",politicstatus = ?,address = ?,phone = ? where id = ?";
        Object[] params = {student.getStunum(), student.getName(), student.getGender(), student.getAge(), student.getNation(), student.getBirthday(),
                student.getGrade(),student.getSpecialty(),student.getClasses(), student.getPoliticstatus(), student.getAddress(), student.getPhone(), student.getId()};
        return this.execute(sql, params);
    }
}
